package com.manager.plat.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.manager.plat.dao.IAnalysisDao;
import com.manager.plat.util.BaseUtil;
import com.manager.plat.util.PageInfo;
import com.manager.plat.vo.AnalysisVo;
import com.manager.plat.vo.UserBillVo;
/**
 * 数据分析数据实现层
 * @author liugeng
 *
 */
public class AnalysisDaoImpl extends JdbcDaoSupport implements IAnalysisDao {
	private static Logger logger = Logger.getLogger(AnalysisDaoImpl.class);
	
	/**根据查询条件获得数据分析信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<AnalysisVo> getProvinceAnalysis(String sp_number, int lower_id, String time_begin, String time_end) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(time_begin);
		paramList.add(time_end);
		
		String sql = "select ub.province, ub.isfilter, count(1)  fee_user_num, sum(ub.minute) fee_minute, " 
					+"ceiling(sum(case when cb.fee_type = 1 then (ub.minute * cb.fee_code/100) when cb.fee_type = 2 then (cb.fee_code/100) else 0 end)) fee_price "
					+"from tb_ivr_user_bill ub "
					+"inner join tb_ivr_config_router cr on cr.id = ub.router_id "
					+"inner join tb_ivr_config_business cb on cb.id = cr.sp_code_id "
					+"where date_format(str_to_date(ub.time_begin,'%Y-%m-%d'),'%Y-%m-%d') >= ? and date_format(str_to_date(ub.time_end,'%Y-%m-%d'),'%Y-%m-%d') <= ? ";
		if(sp_number != null && !"".equals(sp_number) ){
			sql += "and ub.sp_number = ? ";
			paramList.add(sp_number);
		}
		if(lower_id != 0){
			sql += "and ub.lower_id = ? ";
			paramList.add(lower_id);
		}
		sql += "group by ub.province, ub.isfilter ";
		Object[] param = paramList.toArray();
		logger.info(String.format("根据查询条件获得数据分析信息[%s]", BaseUtil.logSQL(sql, param)));
		
		List<AnalysisVo> analysisVoList = this.getJdbcTemplate().query(sql, param, tempMapper);
		if(analysisVoList != null && analysisVoList.size() > 0){
			return analysisVoList;
		}
		
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<AnalysisVo> tempMapper = new ParameterizedRowMapper<AnalysisVo>() {
		@Override
		public AnalysisVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			AnalysisVo analysisVo = new AnalysisVo();
			analysisVo.setProvince(rs.getString("province"));
			analysisVo.setIsfilter(rs.getInt("isfilter"));
			analysisVo.setFee_user_num(rs.getInt("fee_user_num"));
			analysisVo.setFee_minute(rs.getInt("fee_minute"));
			analysisVo.setFee_price(rs.getInt("fee_price"));
			return analysisVo;
		}
	};
	/**根据查询条件获得数据分析信息用户数量*/
	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Integer> getProvinceAnalysisUserNum(String sp_number, int lower_id, String time_begin,String time_end) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(time_begin);
		paramList.add(time_end);
		String sql = "select province, count(mobile) user_num " 
					+"from ( "
					+" select ub.province, ub.mobile "
					+" from tb_ivr_user_bill ub "
					+" where date_format(str_to_date(ub.time_begin,'%Y-%m-%d'),'%Y-%m-%d') >= ? and date_format(str_to_date(ub.time_end,'%Y-%m-%d'),'%Y-%m-%d') <= ? ";
		if(sp_number != null && !"".equals(sp_number) ){
			sql += "and ub.sp_number = ? ";
			paramList.add(sp_number);
		}
		if(lower_id != 0){
			sql += "and ub.lower_id = ? ";
			paramList.add(lower_id);
		}
		sql += "group by ub.province, ub.mobile " +
				") bill " +
				"group by province ";
		Object[] param = paramList.toArray();
		logger.info(String.format("根据查询条件获得数据分析信息用户数量[%s]", BaseUtil.logSQL(sql, param)));
		
		List<AnalysisVo> analysisVoList = this.getJdbcTemplate().query(sql, param, userNumMapper);
		if(analysisVoList != null && analysisVoList.size() > 0){
			Map<String, Integer> analysisVoMap = new HashMap<String, Integer>();
			for(AnalysisVo analysisVo : analysisVoList){
				analysisVoMap.put(analysisVo.getProvince(), analysisVo.getUser_num());
			}
			if(analysisVoMap != null && analysisVoMap.size() > 0){
				return analysisVoMap;
			}
			return null;
		}
		
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<AnalysisVo> userNumMapper = new ParameterizedRowMapper<AnalysisVo>() {
		@Override
		public AnalysisVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			AnalysisVo analysisVo = new AnalysisVo();
			analysisVo.setProvince(rs.getString("province"));
			analysisVo.setUser_num(rs.getInt("user_num"));
			return analysisVo;
		}
	};
	
	/**根据查询条件获得渠道数据分析信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<AnalysisVo> getProvinceAnalysisPartner(String sp_number, int userId, String time_begin, String time_end) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(time_begin);
		paramList.add(time_end);
		paramList.add(userId);
		String sql = "select ub.province, count(1) fee_user_num, sum(ub.minute) fee_minute, " +
					 "ceiling(sum(case when cb.fee_type = 1 then (ub.minute * cb.fee_code/100) when cb.fee_type = 2 then (cb.fee_code/100) else 0 end)) fee_price " +
					 "from tb_ivr_user_bill ub " +
					 "inner join tb_ivr_config_router cr on cr.id = ub.router_id " +
					 "inner join tb_ivr_config_lower_partner clp on clp.id = cr.lower_id " +
					 "inner join tb_ivr_config_business cb on cb.id = cr.sp_code_id " +
					 "where date_format(str_to_date(ub.time_begin,'%Y-%m-%d'),'%Y-%m-%d') >= ? and date_format(str_to_date(ub.time_end,'%Y-%m-%d'),'%Y-%m-%d') <= ? " +
					 "and clp.user_id = ? and ub.isfilter = 0 ";
		if(sp_number != null && !"".equals(sp_number) ){
			sql += "and ub.sp_number = ? ";
			paramList.add(sp_number);
		}
		sql += "group by ub.province ";
		Object[] param = paramList.toArray();
		logger.info(String.format("根据查询条件获得渠道数据分析信息VO列表[%s]", BaseUtil.logSQL(sql, param)));
		
		List<AnalysisVo> analysisVoList = this.getJdbcTemplate().query(sql, param, tempPartnerMapper);
		if(analysisVoList != null && analysisVoList.size() > 0){
			return analysisVoList;
		}
		
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<AnalysisVo> tempPartnerMapper = new ParameterizedRowMapper<AnalysisVo>() {
		@Override
		public AnalysisVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			AnalysisVo analysisVo = new AnalysisVo();
			analysisVo.setProvince(rs.getString("province"));
			analysisVo.setFee_user_num(rs.getInt("fee_user_num"));
			analysisVo.setFee_minute(rs.getInt("fee_minute"));
			analysisVo.setFee_price(rs.getInt("fee_price"));
			return analysisVo;
		}
	};
	/**根据查询条件获得数据分析总数*/
	@Override
	public int getDetailAnalysisTotal(String mobile, String sp_number, int lower_id, String time_begin, String time_end) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(time_begin);
		paramList.add(time_end);
		
		String sql = "select count(0) count from tb_ivr_user_bill ub " +
					 "where date_format(str_to_date(ub.time_begin,'%Y-%m-%d'),'%Y-%m-%d') >= ? and date_format(str_to_date(ub.time_end,'%Y-%m-%d'),'%Y-%m-%d') <= ? ";
		if(mobile != null && !"".equals(mobile) ){
			sql += "and ub.mobile = ? ";
			paramList.add(mobile);
		}
		if(sp_number != null && !"".equals(sp_number) ){
			sql += "and ub.sp_number = ? ";
			paramList.add(sp_number);
		}
		if(lower_id != 0){
			sql += "and ub.lower_id = ? ";
			paramList.add(lower_id);
		}
		Object[] param = paramList.toArray();
		logger.info(String.format("根据查询条件获得数据分析总数[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().queryForInt(sql, param);
		
		return count;
	}
	
	/**根据查询条件获得数据分析数据列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<UserBillVo> getDetailAnalysis(String mobile, String sp_number, int lower_id,String time_begin, String time_end, PageInfo pageInfo) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(time_begin);
		paramList.add(time_end);
		String sql = "select ub.mobile, ub.sp_number, ub.time_begin, ub.time_end, ub.minute, ub.second, ub.province, ub.city, ub.add_date, ub.isfilter, ifnull(dr.status_code,0) dispatcher_status " +
					 "from tb_ivr_user_bill ub " +
					 "left join tb_ivr_dispatcher_result dr on dr.linkId = ub.linkId " +
					 "where date_format(str_to_date(ub.time_begin,'%Y-%m-%d'),'%Y-%m-%d') >= ? and date_format(str_to_date(ub.time_end,'%Y-%m-%d'),'%Y-%m-%d') <= ? ";
		if(mobile != null && !"".equals(mobile) ){
			sql += "and ub.mobile = ? ";
			paramList.add(mobile);
		}
		if(sp_number != null && !"".equals(sp_number) ){
			sql += "and ub.sp_number = ? ";
			paramList.add(sp_number);
		}
		if(lower_id != 0){
			sql += "and ub.lower_id = ? ";
			paramList.add(lower_id);
		}
		
		if(pageInfo != null){
			sql += "order by ub.id desc limit ?, ?";
			paramList.add(pageInfo.getFirstResult() - 1);
			paramList.add(pageInfo.getPageSize());
		}else{
			sql += "order by ub.id desc";
		}
		
		Object[] param = paramList.toArray();
		logger.info(String.format("根据查询条件获得数据分析数据列表[%s]", BaseUtil.logSQL(sql, param)));
		
		List<UserBillVo> userBillVoList = this.getJdbcTemplate().query(sql, param, rowMapper);
		if(userBillVoList != null && userBillVoList.size() > 0){
			return userBillVoList;
		}
		
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<UserBillVo> rowMapper = new ParameterizedRowMapper<UserBillVo>() {
		@Override
		public UserBillVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			UserBillVo userBillVo = new UserBillVo();
			userBillVo.setMobile(rs.getString("mobile"));
			userBillVo.setSp_number(rs.getString("sp_number"));
			userBillVo.setTime_begin(String.valueOf(rs.getTimestamp("time_begin")));
			userBillVo.setTime_end(String.valueOf(rs.getTimestamp("time_end")));
			userBillVo.setMinute(rs.getInt("minute"));
			userBillVo.setSecond(rs.getInt("second"));
			userBillVo.setProvince(rs.getString("province"));
			userBillVo.setCity(rs.getString("city"));
			userBillVo.setAdd_date(rs.getTimestamp("add_date"));
			userBillVo.setIsfilter(rs.getInt("isfilter"));
			userBillVo.setDispatcher_status(rs.getInt("dispatcher_status"));
			return userBillVo;
		}
	};
	
	/**根据查询条件获得渠道数据分析总数*/
	@Override
	public int getDetailAnalysisPartnerTotal(String mobile, String sp_number, int userId, String time_begin,String time_end) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(time_begin);
		paramList.add(time_end);
		paramList.add(userId);
		
		String sql = "select count(0) count from tb_ivr_user_bill ub " +
					 "inner join tb_ivr_config_router cr on cr.id = ub.router_id " +
					 "inner join tb_ivr_config_lower_partner clp on clp.id = cr.lower_id " +
					 "inner join tb_ivr_config_business cb on cb.id = cr.sp_code_id " +
					 "where date_format(str_to_date(ub.time_begin,'%Y-%m-%d'),'%Y-%m-%d') >= ? and date_format(str_to_date(ub.time_end,'%Y-%m-%d'),'%Y-%m-%d') <= ? " +
					 "and clp.user_id = ? and ub.isfilter = 0 ";
		if(mobile != null && !"".equals(mobile) ){
			sql += "and ub.mobile = ? ";
			paramList.add(mobile);
		}
		if(sp_number != null && !"".equals(sp_number) ){
			sql += "and ub.sp_number = ? ";
			paramList.add(sp_number);
		}
		Object[] param = paramList.toArray();
		logger.info(String.format("根据查询条件获得渠道数据分析总数[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().queryForInt(sql, param);
		return count;
	}
	
	/**根据查询条件获得渠道数据分析数据列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<UserBillVo> getDetailAnalysisPartner(String mobile, String sp_number, int userId, String time_begin, String time_end, PageInfo pageInfo) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(time_begin);
		paramList.add(time_end);
		paramList.add(userId);
		
		String sql = "select ub.mobile, ub.sp_number, ub.time_begin, ub.time_end, ub.minute, ub.second, ub.province, ub.city, ub.add_date, ifnull(dr.status_code,0) dispatcher_status " +
					 "from tb_ivr_user_bill ub " +
					 "inner join tb_ivr_config_router cr on cr.id = ub.router_id " +
					 "inner join tb_ivr_config_lower_partner clp on clp.id = cr.lower_id " +
					 "left join tb_ivr_dispatcher_result dr on dr.linkId = ub.linkId " +
					 "where date_format(str_to_date(ub.time_begin,'%Y-%m-%d'),'%Y-%m-%d') >= ? and date_format(str_to_date(ub.time_end,'%Y-%m-%d'),'%Y-%m-%d') <= ? " +
					 "and clp.user_id = ? and ub.isfilter = 0 ";
		
		if(mobile != null && !"".equals(mobile) ){
			sql += "and ub.mobile = ? ";
			paramList.add(mobile);
		}
		if(sp_number != null && !"".equals(sp_number) ){
			sql += "and ub.sp_number = ? ";
			paramList.add(sp_number);
		}
		
		if(pageInfo != null){
			sql += "order by ub.id desc limit ?, ?";
			paramList.add(pageInfo.getFirstResult() - 1);
			paramList.add(pageInfo.getPageSize());
		}else{
			sql += "order by ub.id desc";
		}
		
		Object[] param = paramList.toArray();
		logger.info(String.format("根据查询条件获得渠道数据分析数据列表[%s]", BaseUtil.logSQL(sql, param)));
		
		List<UserBillVo> ivrRealtimeBillVoList = this.getJdbcTemplate().query(sql, param, rowPartnerMapper);
		if(ivrRealtimeBillVoList != null && ivrRealtimeBillVoList.size() > 0){
			return ivrRealtimeBillVoList;
		}
		
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<UserBillVo> rowPartnerMapper = new ParameterizedRowMapper<UserBillVo>() {
		@Override
		public UserBillVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			UserBillVo userBillVo = new UserBillVo();
			userBillVo.setMobile(rs.getString("mobile"));
			userBillVo.setSp_number(rs.getString("sp_number"));
			userBillVo.setTime_begin(String.valueOf(rs.getTimestamp("time_begin")));
			userBillVo.setTime_end(String.valueOf(rs.getTimestamp("time_end")));
			userBillVo.setMinute(rs.getInt("minute"));
			userBillVo.setSecond(rs.getInt("second"));
			userBillVo.setProvince(rs.getString("province"));
			userBillVo.setCity(rs.getString("city"));
			userBillVo.setAdd_date(rs.getTimestamp("add_date"));
			userBillVo.setDispatcher_status(rs.getInt("dispatcher_status"));
			return userBillVo;
		}
	};
	
}
